Fields
Extracted data are stored in fields in the Data Model (see The Data Model). Fields can be present on different levels: on the record level or in a detail table (see Detail tables).
Fields always belong to an Extract step. They can be either location-based, meaning that they read data from a certain location in the data source, or JavaScript-based.
Adding fields
Adding a location-based field
Generally fields are added to a Data Model by extracting data; see Extracting data. Fields in detail tables are created by extracting transactional data; see Extracting transactional data.
Alternatively, you can add fields and detail tables directly in the Data Model pane. Right-click anywhere on the Data Model and a contextual menu will appear. Which menu items are available depends on where you've clicked. If you right-click inside the record itself, you can add a field or a detail table. A field will be added at the end with no extraction, while a detail table will be added with no fields inside.
After adding a field or detail table this way, you can drag & drop data into it. Without data it is not accessible via the Step properties pane.
Adding a JavaScript-based field
JavaScript-based fields are filled by a script: the script provides a value. Note that the last value attribution to a variable is the one used as a result of the expression.
There is a number of ways to add a Javascript based field.
Via the Steps pane
- Make sure there is no data selection in the Data Viewer.
- Right-click on an Extract step on the Steps pane and select Add a Step > Add Extract Field. (To add a new Extract step, select Add a Step > Add Extraction first.)
- On the Step properties pane, under Field Definition, enter the script in the Expression field.
Via the Step properties pane
- Select an Extract step on the Steps pane. (To add a new, empty Extract step, right-click the Steps pane and select Add a Step > Add Extraction.)
- On the Step properties pane, under Field Definition, click the Add JavaScript Field button next to the Field List.
- On the Step properties pane, under Field Definition, enter the script in the Expression field.
By changing a field's mode
Alternatively you can change a location-based into a JavaScript-based field.
- Select the field in the Data Model.
- On the Step properties pane, under Field Definition, change its Mode to JavaScript.
- Enter the script in the Expression field.
Adding fields dynamically
Outside of the DataMapper the Data Model cannot be changed. It isn't possible to add fields to it when using the data mapping configuration in Workflow. It is however possible to add data to existing fields via Workflow; see About adding fields and data via Workflow.
Editing fields
The list of fields that are included in the extraction, the order in which fields are extracted and the data format of each field, are all part of the Extract step's properties. These can be edited via the Step properties pane (see Extract step properties).
Renaming and ordering fields
The order and the names of fields in the Data Model can be changed via the properties of the Extract step that they belong to.
- Select the Extract step that contains the fields that you want to rename. To do this you could click on one of those fields in the Data Model, or on the step in the Steps pane.
- On the Step properties pane, under Field Definition, click the Order and rename fields button.
See Order and rename fields dialog.
Setting the data type
Fields store extracted data as a String by default. The data type of a field can be changed via the properties of the Extract step that the field belongs to.
- Select the Extract step that contains the field. You can do this by clicking on the field in the Data Model, or on the step in the Steps pane that contains the field.
- On the Step properties pane, under Field Definition, set the Type to the desired data type. See Data types for a list of available types.
Changing the type does not only set the data type inside the record. In the case of dates, numbers and currencies, it also means that the DataMapper will expect the data in the data source to be formatted in a certain way. If the actual data doesn't match the format that the DataMapper expects, it cannot interpret the date, number or currency as such. If for example a date in the data source is formatted as "yyyy-mm-dd" but the default format adds the time, the date cannot be read and the DataMapper will stop with an error.
The default format for dates, numbers and currencies can be set in the user preferences (Datamapper preferences), in the data source settings (Data source settings) and per data field (in the Extract step properties, see Data Format).
Setting a default value
You may want to set a default value for a field, in case no extraction can be made. Make sure to set the data type of the field via the step properties (see above). Then right-click the field and select Default Value.
The default value must match the selected data type. If the data type of the field is set to Integer, for example, you cannot enter a value of 2,3. A default date must be formatted as a DateTime object ("year-month-day" plus the time stamp, for example: 2012-04-11 12.00 AM); see Date.
Modifying extracted data
To modify extracted data - the contents of a field - you have to write a script. The script can be entered as a Post function in a location-based field or as an Expression in a JavaScript-based field.
Post function
On the Step properties pane, under Field Definition, you can enter a script in the Post function field to be run after the extraction. (Click the Use JavaScript Editor button to open the Script
Editor dialog if you need more space.)
A Post function script operates directly on the extracted data. Its results replace the extracted data. For example, the Post function script replace("-", ""); replaces the first dash character that occurs inside the extracted string. The code toLowerCase(); converts the string to lowercase letters.
Note that the function must be appropriate for the field's data type.
JavaScript Expression
Alternatively you can change a field's Mode from Location to Javascript:
- Select the field in the Data Model.
- On the Step properties pane, under Field Definition, change its Mode to JavaScript.
You will see that the JavaScript Expression field is not empty; it contains the code that was used to extract data from the location. This code can be used or deleted.
The last value
attribution to a variable is the one used as the result of the
expression.
Deleting a field
The list of fields that are included in an extraction is one of the properties of an Extract step. To delete a field:
- Select the field: click on the field in the Data Model, or select the Extract step that contains the field that you want to delete, and in the Step properties pane, under Field Definition, select the field from the Field List.
- In the Step properties pane, under Field Definition, click the Remove Extract Field button next to the Field List drop-down.
